<?php
// Authenticate
require("class.AuthenticationManager.php");
require("class.CommandMenu.php");
if (!$authenticationManager->isLoggedIn() || !$authenticationManager->hasClearance(CLEARANCE_ADMINISTRATOR)) {
    Header("Location: login.php?redirect=$_SERVER[PHP_SELF]&clearanceRequired=Administrator");
    exit;
}

// Connect to database:
$dbh = dbConnect();

// Load local vars from superglobals:
if (isset($_REQUEST['month']))
{    
    $month = $_REQUEST['month'];
}
if (isset($_REQUEST['year']))
{    
    $year = $_REQUEST['year'];
}
if (isset($_REQUEST['mode']))
{    
    $mode = $_REQUEST['mode'];
}
if (isset($_REQUEST['startDay']))
{    
    $startDay = $_REQUEST['startDay'];
}
if (isset($_REQUEST['startMonth']))
{    
    $startMonth = $_REQUEST['startMonth'];
}
if (isset($_REQUEST['startYear']))
{    
    $startYear = $_REQUEST['startYear'];
}
if (isset($_REQUEST['endDay']))
{    
    $endDay = $_REQUEST['endDay'];
}
if (isset($_REQUEST['endMonth']))
{    
    $endMonth = $_REQUEST['endMonth'];
}
if (isset($_REQUEST['endYear']))
{    
    $endYear = $_REQUEST['endYear'];
}

if (isset($_REQUEST['query']))
{    
    $query = $_REQUEST['query'];
}
if (isset($_REQUEST['queryBis']))
{    
    $queryBis = $_REQUEST['queryBis'];
}

//run the query  
list($qh,$num) = dbQuery($query);
list($qhBis,$numBis) = dbQuery($queryBis);

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once 'Classes/PHPExcel.php';


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Insert Silkan Logo:
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('PHPExcel logo');
$objDrawing->setDescription('PHPExcel logo');
$objDrawing->setPath('./images/silkan.gif');
$objDrawing->setHeight(60);
$objDrawing->setCoordinates('B2');
//$objDrawing->setOffsetX(-10);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

// Merge Cells
$objPHPExcel->getActiveSheet()->mergeCells('B2:B4');

// Set document properties
$objPHPExcel->getProperties()->setCreator("TimeSheet.php")
							 ->setLastModifiedBy("TimeSheet.php")
							 ->setTitle("Office 2007 XLSX Test Document")
							 ->setSubject("Office 2007 XLSX Test Document")
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Test result file");


// Satrt Date & End date:
$startDate = date('D F j, Y',mktime(0,0,0,$startMonth, $startDay, $startYear));
$endDate   = date('D F j, Y',mktime(0,0,0,$endMonth, $endDay, $endYear));
$objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('B7', 'Start date : '.$startDate.'')
                ->setCellValue('B8', 'End date   : '.$endDate.'');



							 
							 
							 
							 
$row = 10;
if ($num == 0) {
    // Add some data
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'No data !');
} else {
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('B10', 'User name')
                ->setCellValue('C10', 'Company')
                ->setCellValue('D10', 'Task name')
                ->setCellValue('E10', 'CIR')
				->setCellValue('F10', 'Source project')
                ->setCellValue('G10', 'Target project')
				->setCellValue('H10', 'Duration');
	

	
	$last_username          = '';
	$last_company           = '';
	$last_task_name         = '';
	$last_source_project    = '';
	$last_target_project    = '';
	$last_duration          = 0 ;
	$current_total_duration = 0;
	
	
	
    $row++;
	while ($data = dbResult($qh)) 
	{
	    
	    
		$current_username       = utf8_encode(stripslashes($data["username"]));
		$current_firstName      = utf8_encode(stripslashes($data["first_name"]));
		$current_lastName       = utf8_encode(stripslashes($data["last_name"]));
		$current_company        = utf8_encode(stripslashes($data["company"]));
		$current_task_name      = utf8_encode(stripslashes($data["name"]));
		$current_cir            = utf8_encode(stripslashes($data["cir"]));
	    $current_source_project = utf8_encode(stripslashes($data["source_project"]));
		$current_target_project = utf8_encode(stripslashes($data["target_project"]));
		$current_duration       = $data["diff"] / 60 / 60 /8;
		
		if( $last_username != $current_username OR $last_task_name != $current_task_name OR $last_source_project != $current_source_project OR $last_target_project != $current_target_project ) 
		{
	        // Add some data
            $str = 'B'.$row.'';
		    $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValueByColumnAndRow(1, $row, ''.$current_firstName.' '.$current_lastName.'');
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
                    
            $str = 'C'.$row.'';
		    $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValueByColumnAndRow(2, $row, $current_company);
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
            
		    $str = 'D'.$row.'';
            $objPHPExcel->setActiveSheetIndex(0)
		    		   ->setCellValueByColumnAndRow(3, $row, $current_task_name);
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
		    	
		    $str = 'E'.$row.'';	
            $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValueByColumnAndRow(4, $row, $current_cir);
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
		    	
	        $str = 'F'.$row.'';
            $objPHPExcel->setActiveSheetIndex(0)
		    			->setCellValueByColumnAndRow(5, $row, $current_source_project);
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
		   			
		    $str = 'G'.$row.'';
            $objPHPExcel->setActiveSheetIndex(0)			
		    			->setCellValueByColumnAndRow(6, $row, $current_target_project);
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));	
			
			$current_total_duration = 0;	 
			$current_total_duration = $current_total_duration + $current_duration;
			$row++;	
        } else {
		    
			$current_total_duration = $current_total_duration + $current_duration;
		    
		    
		}
			$rowBis = $row -1;
			$str = 'H'.$rowBis.'';
            $objPHPExcel->setActiveSheetIndex(0)			
		    			->setCellValueByColumnAndRow(7, $rowBis, $current_total_duration);
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
	
		$last_username       = $current_username;	
        $last_task_name      = $current_task_name;
		$last_source_project = $current_source_project;
		$last_target_project = $current_target_project;		
	    
	}
}
// Without Imputation 
if ($numBis != 0) 
{
    while ($dataBis = dbResult($qhBis)) 
	{
	    $isExist = 0;
	    list($qh,$num) = dbQuery($query);
		while ($data = dbResult($qh)) 
	    {
			
		    if($data["first_name"] == $dataBis["first_name"] AND $data["last_name"] == $dataBis["last_name"] AND $data["company"] == $dataBis["company"] AND $data["name"] == $dataBis["name"] AND $data["source_project"] == $dataBis["source_project"] AND $data["target_project"] == $dataBis["target_project"])
			{
			    $isExist = 1;
			}
		}
		if ($isExist == 0)
		{
			// Add some data
            $str = 'B'.$row.'';
		    $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValueByColumnAndRow(1, $row, ''.utf8_encode(stripslashes($dataBis["first_name"])).' '.utf8_encode(stripslashes($dataBis["last_name"])).'');
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
                    
            $str = 'C'.$row.'';
		    $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValueByColumnAndRow(2, $row, utf8_encode(stripslashes($dataBis["company"])));
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
            
		    $str = 'D'.$row.'';
            $objPHPExcel->setActiveSheetIndex(0)
		    		   ->setCellValueByColumnAndRow(3, $row, utf8_encode(stripslashes($dataBis["name"])));
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
		    	
		    $str = 'E'.$row.'';	
            $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValueByColumnAndRow(4, $row, utf8_encode(stripslashes($dataBis["cir"])));
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
		    	
	        $str = 'F'.$row.'';
            $objPHPExcel->setActiveSheetIndex(0)
		    			->setCellValueByColumnAndRow(5, $row, utf8_encode(stripslashes($dataBis["target_project"])));
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
		   			
		    $str = 'G'.$row.'';
            $objPHPExcel->setActiveSheetIndex(0)			
		    			->setCellValueByColumnAndRow(6, $row, utf8_encode(stripslashes($dataBis["target_project"])));
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
            
			$str = 'H'.$row.'';
            $objPHPExcel->setActiveSheetIndex(0)			
		    			->setCellValueByColumnAndRow(7, $row, 0);
		    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
			$row++;	
		}
		
    }
}


// Start date & End date:
$objPHPExcel->getActiveSheet()->getStyle('B7')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('B7')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getStyle('B8')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('B8')->getFont()->setBold(true);

// Set fonts

			
$objPHPExcel->getActiveSheet()->getStyle('B10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('B10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('B10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('C10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('C10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('C10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('C10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('D10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('D10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('D10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('D10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('E10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('E10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('E10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));			

$objPHPExcel->getActiveSheet()->getStyle('F10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('F10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('F10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('G10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('G10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('G10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('G10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));			

$objPHPExcel->getActiveSheet()->getStyle('H10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('H10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('H10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('H10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
				
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Report_annual_state_R_and_D');


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Report_annual_state_R_and_D.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
